import pymysql


def create_connection():
    """创建数据库连接"""
    try:
        conn = pymysql.connect(
            host='localhost',
            user='root',
            password='123456',
            database='sys',
            port=3306,
            charset='utf8'
        )
        return conn
    except Exception as err:
        print(f"数据库连接错误: {err}")
        exit(1)


def create_table(conn):
    """创建person表"""
    cursor = conn.cursor()
    create_table_query = """
    CREATE TABLE IF NOT EXISTS person (
        username VARCHAR(20),
        age INT,
        sex CHAR(4),
        high DOUBLE(3,2)
    )
    """
    try:
        cursor.execute(create_table_query)
        conn.commit()
        print("表创建成功或已存在")
    except Exception as e:
        print(f"创建表错误: {e}")
        exit(1)
    finally:
        cursor.close()


def input_person_data():
    """输入一个人的信息"""
    username = input("请输入姓名: ")

    while True:
        try:
            age = int(input("请输入年龄: "))
            if age <= 0 or age > 120:
                print("年龄必须在1-120之间")
                continue
            break
        except ValueError:
            print("请输入有效的整数")

    while True:
        sex = input("请输入性别(男/女): ")
        if sex in ["男", "女"]:
            break
        print("性别必须是'男'或'女'")

    while True:
        try:
            high = float(input("请输入身高(米): "))
            if 0.5 <= high <= 2.5:  # 身高在0.5米到2.5米之间
                break
            print("身高必须在0.5-2.5米之间")
        except ValueError:
            print("请输入有效的数字")

    return username, age, sex, high


def insert_person_data(conn, data):
    """插入数据到数据库"""
    cursor = conn.cursor()
    insert_query = """
    INSERT INTO person (username, age, sex, high)
    VALUES (%s, %s, %s, %s)
    """
    try:
        cursor.execute(insert_query, data)
        conn.commit()
        print("数据插入成功!")
    except Exception as e:
        print(f"数据插入错误: {e}")
    finally:
        cursor.close()


def main():
    # 创建数据库连接
    conn = create_connection()

    # 创建表
    create_table(conn)

    # 输入10个人的信息
    for i in range(1, 11):
        print(f"\n输入第{i}个人的信息:")
        person_data = input_person_data()
        insert_person_data(conn, person_data)

    # 关闭连接
    conn.close()
    print("\n所有数据已成功存储到数据库!")


if __name__ == "__main__":
    main()